﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace AccessControl.Data
{
    #region "Enumerations"
    #endregion

    public class FingerDetectionLog : Entity
    {


        #region "Properties"

        public int fkEventLog { get; set; }
        public int fkWorkflow { get; set; }
        public string fkPerson { get; set; }
        public long? fkGuest { get; set; }
        public byte Result { get; set; }
        public string Reason { get; set; }
        public DateTime DateRegistered { get; set; }
        #endregion

        #region Constructors
        #endregion

        #region "DataAccess"

        public static FingerDetectionLog LoadLog(int fkEventLog)
        {
            var SqlCmd = new SqlCommand("select * from tblFingerDetectionLog where fkEventLog=@fkEventLog");
            SqlCmd.Parameters.Add("@fkEventLog", SqlDbType.Int).Value = fkEventLog;
            var TempFingerDetectionLog = new FingerDetectionLog();
            return FindSingleItem(SqlCmd, TempFingerDetectionLog) ? TempFingerDetectionLog : null;
        }

        protected override bool Insert()
        {
            var cmd = new SqlCommand();
            WriteValues(cmd.Parameters);
            cmd.CommandText = "INSERT INTO tblFingerDetectionLog " + GetInsertPart(cmd.Parameters);
            Connection.ExecuteScalar(cmd);
            return true;

        }

        protected override bool Update()
        {
            var cmd = new SqlCommand();
            WriteValues(cmd.Parameters);
            cmd.CommandText = "UPDATE tblFingerDetectionLog SET " + GetUpdatePart(cmd.Parameters) + " WHERE fkEventLog=@fkEventLog";
            return Connection.ExecuteNonQuery(cmd) > 0;
        }

        public bool Delete()
        {
            var cmd = new SqlCommand("DELETE FROM tblFingerDetectionLog WHERE fkEventLog=@fkEventLog");
            cmd.Parameters.Add("@fkEventLog", SqlDbType.Int).Value = fkEventLog;
            if (Connection.ExecuteNonQuery(cmd) > 0)
            {
                Exists = false;
                return true;
            }
            return true;
        }

        public override void ReadValues(DataRow rdr)
        {
            fkEventLog = (int)rdr["fkEventLog"];
            fkWorkflow = (int)rdr["fkWorkflow"];
            fkPerson = (string)rdr["fkPerson"];
            fkGuest = rdr["fkGuest"] as long?;
            Result = (byte)rdr["Result"];
            Reason = (string)rdr["Reason"];
            DateRegistered = (DateTime)rdr["DateRegistered"];
        }

        protected override void WriteValues(SqlParameterCollection parameters)
        {
            parameters.Add("@fkWorkflow", SqlDbType.Int).Value = fkWorkflow;
            parameters.Add("@fkPerson", SqlDbType.VarChar, 20).Value = fkPerson;
            parameters.Add("@fkGuest", SqlDbType.Bigint).Value = fkGuest ?? (object)DBNull.Value;
            parameters.Add("@Result", SqlDbType.TinyInt).Value = Result;
            parameters.Add("@Reason", SqlDbType.NVarChar, 300).Value = Reason;
            parameters.Add("@DateRegistered", SqlDbType.DateTime).Value = DateRegistered;
        }

        #endregion


        public static DataTable FillLogs(string filter, int groupType, DateTime startDate, DateTime endDate)
        {
            var cmd = new SqlCommand(string.Format("SELECT tblPerson.PersonRefNumber,tblPerson.PersonFirstName,tblPerson.PersonLastName, tblWorkflow.WorkflowName,tblFingerDetectionLog.Reason,tblFingerDetectionLog.Result, tblFingerDetectionLog.DateRegistered,tblEvent.EventName,tblEventType.EventTypeName,tblGroup.GroupName FROM tblFingerDetectionLog INNER JOIN tblWorkflow ON tblWorkflow.pkWorkflow=tblFingerDetectionLog.fkWorkflow INNER JOIN tblPerson ON tblPerson.pkPerson=tblFingerDetectionLog.fkPerson INNER JOIN tblEvent ON tblWorkflow.fkEvent=tblEvent.pkEvent INNER JOIN tblEventType ON tblEvent.fkEventType=tblEventType.pkEventType LEFT JOIN tblPersonGroup INNER JOIN tblGroup ON tblPersonGroup.fkGroup=tblGroup.pkGroup AND tblPersonGroup.fkGroupType=@GroupType AND tblPersonGroup.fkGroupType=tblGroup.fkGroupType ON tblPerson.pkPerson= tblPersonGroup.fkPerson WHERE tblFingerDetectionLog.DateRegistered >= @StartDate AND tblFingerDetectionLog.DateRegistered<= @EndDate {0}", filter));
            cmd.Parameters.AddWithValue("@StartDate", startDate);
            cmd.Parameters.AddWithValue("@EndDate", endDate);
            cmd.Parameters.AddWithValue("@GroupType", groupType);
            return Connection.GetDataTable(cmd);
        }


    }
}
